﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Services;
using System.Web.Services;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;

public partial class Peneliti_Proposal : System.Web.UI.Page
{
    private static string strConn = ConfigurationManager.ConnectionStrings["LabConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        populateTahunAnggaran();
        populateBarangPenunjang();
        populateBarang();
        submitProposal();
        cetakProposalPDF();
    }

    private static Font font10 = new Font(Font.COURIER, 10f, Font.NORMAL);
    private static Font font10b = new Font(Font.COURIER, 10f, Font.BOLD);
    private static Font font12 = new Font(Font.COURIER, 12f, Font.NORMAL);
    private static Font font12b = new Font(Font.COURIER, 12f, Font.BOLD);
    private static Font font13b = new Font(Font.COURIER, 13f, Font.BOLD);
    private static Font font14b = new Font(Font.COURIER, 14f, Font.BOLD);

    private void addRowDetailCell(PdfPTable rincianTable, string text, bool isLast = false, bool isNumeric = false)
    {
        rincianTable.AddCell(new PdfPCell(new Phrase(text, font10)) { PaddingTop = 0, PaddingBottom = 5, HorizontalAlignment = isNumeric ? 2 : 0, BorderWidthTop = 0, BorderWidthBottom = 1, BorderWidthLeft = 1, BorderWidthRight = isLast ? 1 : 0 });
    }

    private void addRowGroupCell(PdfPTable rincianTable, string text)
    {
        rincianTable.AddCell(new PdfPCell(new Phrase(text, font10b)) { BackgroundColor = Color.LIGHT_GRAY, Colspan=5, PaddingTop = 0, PaddingBottom = 5, HorizontalAlignment = 0, BorderWidthTop = 1, BorderWidthBottom = 1, BorderWidthLeft = 1, BorderWidthRight = 1 });
    }

    private void submitProposal()
    {
        string tahun = Request.Form["tahun"];
        string kodeproposal = Request.Form["kodeproposal"];
        string barang = Request.Form["barang[]"];
        string jumlah = Request.Form["jumlah[]"];
        string keterangan = Request.Form["keterangan[]"];
        string user = Session["idUser"].ToString();

        if (string.IsNullOrEmpty(tahun) || string.IsNullOrEmpty(kodeproposal) || 
            string.IsNullOrEmpty(barang) || string.IsNullOrEmpty(jumlah) || 
            string.IsNullOrEmpty(keterangan) || string.IsNullOrEmpty(user))
        {
            return;
        }

        string sql = "sp_submit_proposal " + tahun + ", '" + kodeproposal + "', '" + barang + "', '" + jumlah + "', '" + keterangan + "', " + user;
        try
        {
            using (var cn = new SqlConnection(strConn))
            {
                cn.Open();
                using (var cmd = new SqlCommand(sql, cn))
                {
                    var ra = cmd.ExecuteNonQuery();
                    if (ra > 0)
                    {
                        showMessage("Cetak Proposal", "Proposal berhasil dikirim, tunggu beberapa saat hingga proses download selesai.", "success");
                        //success --> cetak
                        ClientScript.RegisterStartupScript(GetType(), "OpenWindow", "setTimeout(function() {window.open('Peneliti_Proposal.aspx?kode=" + kodeproposal + "', '_self'); }, 2000);", true);
                    }
                }
                cn.Close();
            }
        }
        catch (Exception ex)
        {
            var message = ex.Message;
            if (message.Contains("Cannot insert duplicate key row"))
            {
                message = "Proposal dengan kode yang sama sudah ada. (" + kodeproposal + ")";
            }
            showMessage("Error", message, "error");
        }
    }

    private void cetakProposalPDF()
    {
        string kodeProposal = Request.QueryString["kode"];
        if (string.IsNullOrEmpty(kodeProposal))
        {
            return;
        }

        DataSet ds = new DataSet();
        using (var da = new SqlDataAdapter("sp_cetak_proposal '" + kodeProposal + "'", strConn))
        {
            da.Fill(ds);
        }

        if (ds.Tables.Count < 3)
        {
            return;
        }

        DataTable dtHeader = ds.Tables[0];
        DataTable dtKimia = ds.Tables[1];
        DataTable dtPenunjang = ds.Tables[2];
        
        Document doc = new Document();
        doc.SetPageSize(PageSize.A4);
        doc.SetMargins(50f, 50f, 10f, 10f);
        MemoryStream ms = new MemoryStream();
        var writer = PdfWriter.GetInstance(doc, ms);
        doc.Open();

        PdfPTable allHeaderTable = new PdfPTable(2) { WidthPercentage = 100 };
        int wh1 = (int)(0.1 * PageSize.A4.Width);
        int wh2 = (int)(0.9 * PageSize.A4.Width);
        allHeaderTable.SetWidths(new[] { wh1, wh2 });

        PdfPTable headerTable = new PdfPTable(1);
        headerTable.AddCell(new PdfPCell(new Phrase(dtHeader.Rows[0]["line1"].ToString(), font12b)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });
        headerTable.AddCell(new PdfPCell(new Phrase(dtHeader.Rows[0]["line2"].ToString(), font10)) { HorizontalAlignment = 1, BorderWidth = 0 });
        headerTable.AddCell(new PdfPCell(new Phrase(dtHeader.Rows[0]["line3"].ToString(), font12)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });
        headerTable.AddCell(new PdfPCell(new Phrase(dtHeader.Rows[0]["line4"].ToString(), font14b)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });

        Image logo = Image.GetInstance(Server.MapPath(".") + @"\images\balitvet.png");
        logo.WidthPercentage = 10;

        allHeaderTable.AddCell(new PdfPCell(logo, true) { BorderWidth = 0 });
        allHeaderTable.AddCell(new PdfPCell(headerTable) { UseBorderPadding = false, BorderWidth = 0 });
        allHeaderTable.AddCell(new PdfPCell(new Phrase("Bagian : " + dtHeader.Rows[0]["bagian"].ToString() , font12b)) { HorizontalAlignment = 0, BorderWidth = 0, PaddingBottom = 5, Colspan = 2 });

        doc.Add(allHeaderTable);
        PdfPTable rincianTable = new PdfPTable(5) { WidthPercentage = 100 };
        int w1 = (int)(0.08  * PageSize.A4.Width);
        int w2 = (int)(0.4   * PageSize.A4.Width);
        int w3 = (int)(0.175 * PageSize.A4.Width);
        int w4 = (int)(0.175 * PageSize.A4.Width);
        int w5 = (int)(0.17  * PageSize.A4.Width);
        rincianTable.SetWidths(new[] { w1, w2, w3, w4, w5 });

        rincianTable.AddCell(new PdfPCell(new Phrase("No."         , font10b)) { PaddingTop = 5, PaddingBottom = 5, HorizontalAlignment = 1, BorderWidthLeft = 1, BorderWidthTop = 1, BorderWidthBottom = 1, BorderWidthRight = 0 });
        rincianTable.AddCell(new PdfPCell(new Phrase("Nama Barang" , font10b)) { PaddingTop = 5, PaddingBottom = 5, HorizontalAlignment = 1, BorderWidthLeft = 1, BorderWidthTop = 1, BorderWidthBottom = 1, BorderWidthRight = 0 });

        PdfPTable banyaknyaTable = new PdfPTable(2) { SpacingAfter = 0, SpacingBefore = 0 };
        banyaknyaTable.AddCell(new PdfPCell(new Phrase("Banyaknya", font10b)) { Colspan=2, PaddingTop = 5, PaddingBottom = 5, HorizontalAlignment = 1, BorderWidthTop = 0, BorderWidthBottom = 1, BorderWidthLeft = 0, BorderWidthRight = 0 });
        banyaknyaTable.AddCell(new PdfPCell(new Phrase("Permintaan", font10b)) { PaddingTop = 5, PaddingBottom = 5, HorizontalAlignment = 1, BorderWidthTop = 0, BorderWidthBottom = 0, BorderWidthLeft = 0, BorderWidthRight = 1 });
        banyaknyaTable.AddCell(new PdfPCell(new Phrase("Diberikan", font10b)) { PaddingTop = 5, PaddingBottom = 5, HorizontalAlignment = 1, BorderWidthTop = 0, BorderWidthBottom = 0, BorderWidthLeft = 0, BorderWidthRight = 0 });
        rincianTable.AddCell(new PdfPCell(banyaknyaTable) { Colspan = 2, UseBorderPadding = false, BorderWidthLeft = 1, BorderWidthTop = 1, BorderWidthBottom = 1, BorderWidthRight = 0 });
        rincianTable.AddCell(new PdfPCell(new Phrase("Keterangan"  , font10b)) { PaddingTop = 5, PaddingBottom = 5, HorizontalAlignment = 1, BorderWidthTop = 1, BorderWidthBottom = 1, BorderWidthLeft = 1, BorderWidthRight = 1 });

        if (dtKimia.Rows.Count > 0)
        {
            addRowGroupCell(rincianTable, "Bahan Kimia");
            foreach (DataRow item in dtKimia.Rows)
            {
                addRowDetailCell(rincianTable, item["nomor"].ToString(), false, true);
                addRowDetailCell(rincianTable, item["nama"].ToString());
                addRowDetailCell(rincianTable, item["permintaan"].ToString(), false, true);
                addRowDetailCell(rincianTable, item["diberikan"].ToString(), false, true);
                addRowDetailCell(rincianTable, item["keterangan"].ToString(), true);
            }            
        }

        if (dtPenunjang.Rows.Count > 0)
        {
            addRowGroupCell(rincianTable, "Bahan Penunjang");
            foreach (DataRow item in dtPenunjang.Rows)
            {
                addRowDetailCell(rincianTable, item["nomor"].ToString(), false, true);
                addRowDetailCell(rincianTable, item["nama"].ToString());
                addRowDetailCell(rincianTable, item["permintaan"].ToString(), false, true);
                addRowDetailCell(rincianTable, item["diberikan"].ToString(), false, true);
                addRowDetailCell(rincianTable, item["keterangan"].ToString(), true);
            }
        }

        doc.Add(rincianTable);

        PdfPTable footerTable = new PdfPTable(3) { WidthPercentage = 100 };
        int fw = (int)(0.33 * PageSize.A4.Width);
        footerTable.SetWidths(new[] { fw, fw, fw });
        footerTable.AddCell(new PdfPCell() { BorderWidth = 0, });
        footerTable.AddCell(new PdfPCell() { BorderWidth = 0, });
        footerTable.AddCell(new PdfPCell(new Phrase("Bogor, " + dtHeader.Rows[0]["tanggal"].ToString(), font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5, PaddingTop = 5 });

        footerTable.AddCell(new PdfPCell(new Phrase("Mengetahui/Menyetujui PPK", font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });
        footerTable.AddCell(new PdfPCell(new Phrase("Mengetahui :", font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });
        footerTable.AddCell(new PdfPCell(new Phrase("Yang Meminta", font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });

        footerTable.AddCell(new PdfPCell() { BorderWidth = 0, });
        footerTable.AddCell(new PdfPCell(new Phrase("Kepala Bagian", font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });
        footerTable.AddCell(new PdfPCell() { BorderWidth = 0, });

        footerTable.AddCell(new PdfPCell() { BorderWidth = 0, });
        footerTable.AddCell(new PdfPCell() { BorderWidth = 0, });
        footerTable.AddCell(new PdfPCell() { BorderWidth = 0, FixedHeight = 25 });

        footerTable.AddCell(new PdfPCell(new Phrase("(.....................)", font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });
        footerTable.AddCell(new PdfPCell(new Phrase("(.....................)", font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });
        footerTable.AddCell(new PdfPCell(new Phrase(dtHeader.Rows[0]["nama"].ToString(), font10)) { HorizontalAlignment = 1, BorderWidth = 0, PaddingBottom = 5 });

        doc.Add(footerTable);
        doc.Close();

        Response.Clear();
        //Response.ContentType = "application/pdf";
        Response.ContentType = "application/octet-stream";
        Response.AppendHeader("Content-Disposition", "attachment; filename=proposal_" + kodeProposal + ".pdf");
        Response.BinaryWrite(ms.ToArray());
    }

    private void showMessage(string caption, string message, string type)
    {
        message = message.Replace("'", "&apos;");
        message = message.Replace(Environment.NewLine, "<br />");
        string clientScript = "$(document).ready(function(){ new PNotify({ title: '" + caption + "', text: '" + message + "', type: '" + type + "',styling: 'bootstrap3' }); });";
        System.Web.UI.ScriptManager.RegisterStartupScript(Page, this.GetType(), "ShowMessage", clientScript, true);
    }

    private void populateTahunAnggaran()
    {
        string query = "sp_get_tahun_anggaran";
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(query, strConn))
        {
            da.Fill(dt);
        }
        DataTable tahun = new DataTable();
        tahun.Columns.Add("TAHUN");
        if (dt.Rows.Count > 0)
        {
            int min = int.Parse(dt.Rows[0]["TAHUN1"].ToString());
            int max = int.Parse(dt.Rows[0]["TAHUN2"].ToString());
            for (int i = min; i <= max; i++)
            {
                DataRow dr = tahun.NewRow();
                dr["TAHUN"] = i;
                tahun.Rows.Add(dr);
            }
        }
        lstTahunAnggaran.DataSource = tahun;
        lstTahunAnggaran.DataBind();
    }

    private void populateBarang()
    {
        string query = "sp_namabarangKimia_list";
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(query, strConn))
        {
            da.Fill(dt);
        }
        lstBarang.DataSource = dt;
        lstBarang.DataBind();
    }

    private void populateBarangPenunjang()
    {
        string query = "sp_namabarangPenunjang_list";
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(query, strConn))
        {
            da.Fill(dt);
        }
        lstBarangenunjang.DataSource = dt;
        lstBarangenunjang.DataBind();
    }

    [WebMethod, ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = false)]
    public static string GetUnitString(int unit)
    {
        string result = string.Empty;
        DataTable dt = new DataTable();
        string query = "sp_getunit {0}";
        using (SqlDataAdapter da = new SqlDataAdapter(string.Format(query, unit), strConn))
        {
            da.Fill(dt);
        }

        foreach (DataRow item in dt.Rows)
        {
            result = item["SATUAN"].ToString();
        }
        return result;
    }
}